const fs = require('fs');
const path = require('path');
const StreamZip = require('node-stream-zip');
const iconv = require('iconv-lite');
const sqlite3 = require('sqlite3').verbose();
const fse = require('fs-extra');

// 解压zip文件
function extractZipFile(zipFilePath, extractPath) {
  return new Promise((resolve, reject) => {
    const zip = new StreamZip({
      file: zipFilePath,
      storeEntries: true
    });

    zip.on('ready', () => {
      zip.extract(null, extractPath, (err) => {
        if (err) {
          reject(err);
        } else {
          zip.close();
          resolve();
        }
      });
    });

    zip.on('error', (err) => {
      reject(err);
    });
  });
}

// 递归查找目录下的CSV文件
function findCSVFiles(directory, filelist = []) {
  const files = fs.readdirSync(directory);
  for (const file of files) {
    const filePath = path.join(directory, file);
    const stat = fs.statSync(filePath);
    if (stat.isDirectory()) {
      findCSVFiles(filePath, filelist);
    } else if (path.extname(file) === '.csv') {
      filelist.push(filePath);
    }
  }
  return filelist;
}

// 数据过滤器
function dataFilter(data, filterRules) {
  return filterRules.every((rules) => {
    return rules.every((rule) => {
      const columnIndex = rule[0];
      const columnValue = rule[1];
      return data[columnIndex] !== columnValue;
    });
  });
}

//账户信息数据 卡号
function accountInfos_k(accountCSVFiles) {
  const result = {};

  for (const file of accountCSVFiles) {
    const fileDataBuffer = fs.readFileSync(file);
    const fileData = iconv.decode(fileDataBuffer, 'gbk').trim().replace(/[\t'"]/g, '');
    const rows = fileData.split('\n').map(row => row.split(','));

    for (const row of rows) {
      const columnIndex = 2;
      const valueIndex = 0;
      const columnIndexValid = columnIndex < row.length;
      const valueIndexValid = valueIndex < row.length;

      if (columnIndexValid && valueIndexValid) {
        let columnValue = row[columnIndex];
        const value = row[valueIndex];

        if (columnValue.includes('_')) {
          columnValue = columnValue.split('_')[0]; // 截取第一个下划线之前的值作为索引
        }

        result[columnValue] = value;
      }
    }
  }

  return result;
}


//账户信息数据 账号
function accountInfos_z(accountCSVFiles) {
  const result = {};

  for (const file of accountCSVFiles) {
    const fileDataBuffer = fs.readFileSync(file);
    const fileData = iconv.decode(fileDataBuffer, 'gbk').trim().replace(/[\t'"]/g, '');
    const rows = fileData.split('\n').map(row => row.split(','));

    for (const row of rows) {
      const columnIndex = 3;
      const valueIndex = 0;
      const columnIndexValid = columnIndex < row.length;
      const valueIndexValid = valueIndex < row.length;

      if (columnIndexValid && valueIndexValid) {
        let columnValue = row[columnIndex];
        const value = row[valueIndex];

        if (columnValue.includes('_')) {
          columnValue = columnValue.split('_')[0]; // 截取第一个下划线之前的值作为索引
        }

        result[columnValue] = value;
      }
    }
  }

  return result;
}

function findZIPFiles(directory, filelist = []) {
  const files = fs.readdirSync(directory);
  for (const file of files) {
    const filePath = path.join(directory, file);
    const stat = fs.statSync(filePath);
    if (stat.isDirectory()) {
      findZIPFiles(filePath, filelist);
    } else if (path.extname(file) === '.zip') {
      filelist.push(filePath);
    }
  }
  return filelist;
}

function findZipAndunzip(zippath,extractPath) {
  const zipFiles = findZIPFiles(zippath);
  console.log(zipFiles)
  console.log(extractPath)

  for (const zipfile of zipFiles) {
    extractZipFile(zipfile,extractPath);
  }

}


// 主函数
async function processCSVFiles(zipFilePath, extractPath, dbFilePath, tableName, batchSize = 20000, filterRules = []) {
  try {

    // 解压zip文件
    await extractZipFile(zipFilePath, extractPath);

    await findZipAndunzip(extractPath,extractPath);
    // 查找CSV文件
    const csvFiles = findCSVFiles(extractPath);
    const filteredCSVFiles = csvFiles.filter((file) => file.includes('交易明细信息'));
    //用户信息csv 列表
    const accountCSVFiles = csvFiles.filter(file => {
      const fileName = path.basename(file);
      return fileName.includes('账户信息') && !fileName.includes('子账户信息');
    });
    //账户信息字典
    const accountData_k = accountInfos_k(accountCSVFiles) //卡号
    const accountData_z = accountInfos_z(accountCSVFiles) //账号
    console.log(filteredCSVFiles)

    // 逐个处理CSV文件
    const dbData = [];
    const headerString =  `j_y_k_h,j_y_z_h,c_x_f_k_j_g_y_y_y,j_y_h_m,j_y_z_j_h_m,j_y_s_j,j_y_j_e,j_y_y_e,s_f_b_z,j_y_d_s_z_h,x_j_b_z,d_s_h_m,d_s_s_f_z_h,d_s_k_h_y_h,z_h_s_m,j_y_b_z,j_y_w_d_m_c,j_y_f_s_d,j_y_s_f_c_g,p_h,IP_d_z,MAC_d_z,d_s_j_y_y_e,j_y_l_s_h,r_z_h,p_z_z_l,p_z_h,j_y_g_y_h_h,b_z,s_h_m_c,s_h_d_m,j_y_l_x`;
    const header = headerString.split(",");
    for (const file of filteredCSVFiles) {
      const fileDataBuffer = fs.readFileSync(file);
      const fileData = iconv.decode(fileDataBuffer, 'gbk').trim().replace(/[\t'"]/g, '');
      const rows = fileData.split('\n').map((row) => row.split(','));

      const filteredRows = rows.slice(1).filter((row) => dataFilter(row, filterRules));
      const formattedData = filteredRows.map((row) => {
        const rowData = {};
        header.forEach((column, index) => {
          if (index == 3) {
            const index_k = row[0].trim()
            const index_z = row[1].trim()
            const kh = accountData_k[index_k]
            const zh = accountData_z[index_z]
            rowData[column] = kh
            if (accountData_k.hasOwnProperty(index_k)) {
              rowData[column] = kh
            } else if (accountData_z.hasOwnProperty(index_z)){
              rowData[column] = zh
            } else {
              rowData[column] = ''
            }
          } else {
            rowData[column] = row[index];
          }
        });
        return Object.values(rowData);
      });
      dbData.push(...formattedData);

      // 分批插入数据
      if (dbData.length >= batchSize) {
        const sqlStatements = dbData
          .splice(0, batchSize)
          .map((data) => `INSERT INTO ${tableName} (${header.join(',')}) VALUES (${data.map((value) => `"${value}"`).join(',')});`);
        await executeSQLStatements(dbFilePath, sqlStatements);
      }
    }

    // 插入剩余数据
    if (dbData.length > 0) {
      const sqlStatements = dbData
        .map((data) => `INSERT INTO ${tableName} (${header.join(',')}) VALUES (${data.map((value) => `"${value}"`).join(',')});`);
      await executeSQLStatements(dbFilePath, sqlStatements);
    }
// 删除extractPath文件夹中的内容
    await fse.emptyDir(extractPath);
    process.send('CSV文件处理完成并成功插入数据库。')
  } catch (err) {
    console.log('处理CSV文件时出现错误:', err)
  }
}

function executeSQLStatements(dbFilePath, sqlStatements) {
  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(dbFilePath);
    db.serialize(() => {
      db.run('BEGIN TRANSACTION;'); // 开始事务

      sqlStatements.forEach((sql, index) => {
        db.run(sql, function (err) {
          if (err) {
            db.run('ROLLBACK;'); // 回滚事务
            db.close();
            reject(err);
          } else if (index === sqlStatements.length - 1) {
            db.run('COMMIT;'); // 提交事务
            db.close();
            resolve();
          }
        });
      });
    });
  });
}

// 使用示例

const filterRules = [
  [[0, ''], [1, ''], [2, '']],
  [[2, '客户信息不存在']],
  [[2, '没有符合条件的记录。']],
  [[2, '找不到客户']],

];

const zipFilePath = process.argv[2];
const extractPath = process.argv[3];
const dbFilePath = process.argv[4];
const tableName = process.argv[5];
processCSVFiles(zipFilePath, extractPath, dbFilePath, tableName, 20, filterRules);
